﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using WebService.VO;

namespace WebService.DAO
{
    public class CategoryDAO
    {
        private dbConnection conn;
        /// <summary>
        /// khởi tạo đối tượng xử lý dl
        /// </summary>
        public CategoryDAO()
        {
            conn = new dbConnection();
        }
        /// <summary>
        /// lấy danh sách danh mục
        /// </summary>
        /// <returns></returns>

        public DataTable getCategoryList(string language)
        {
            if (language.Equals("vi"))
            {
                return conn.GetDataTable("Select CategoryID, CategoryName From tbl_Category");
            }
            return conn.GetDataTable("Select a.CategoryID, Name From tbl_Category a, tbl_CategoryTranslation b Where a.CategoryID=b.CategoryID And Language='" + language+"'");
        }

        public CategoryVO getCategory(string id)
        {
            CategoryVO cate=new CategoryVO();
            DataTable dt;
            dt = conn.GetDataTable("Select * From tbl_Category Where CategoryID="+id);
            if (dt != null)
            {
                if (dt.Rows[0] != null)
                {
                    cate.CategoryId = Convert.ToInt16(dt.Rows[0]["CategoryID"].ToString());
                    if (dt.Rows[0]["CategoryID"].ToString() != null)
                    {
                        cate.CategoryName = dt.Rows[0]["CategoryName"].ToString();
                    }
                    else
                    {
                        cate.CategoryName = "NULL";
                    }
                    
                }
            }
            return cate;
        }

        public bool checklang(string categoryid, string lang)
        {
            string sqlSelect = "Select count(*) From tbl_CategoryTranslation Where Language=@Lang AND CategoryID=@CategoryID";
            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@Lang", lang);
            d.Add("@CategoryID", categoryid);

            int result = conn.ExecuteScalar(sqlSelect, d, false);
            if (result > 0)
                return true;
            return false;
        }

        /// <summary>
        /// Thêm mới 1 danh mục
        /// </summary>
        public void insert(CategoryVO cate)
        {
            string sqlInsert = "INSERT INTO tbl_Category VALUES(@Name)";

            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@Name", cate.CategoryName);

            conn.ExecuteQuery(sqlInsert, d, false);

        }

        /// <summary>
        /// Xóa thông tin một danh mục
        /// </summary>
        public void delete(string id)
        {
            string sql = "DELETE FROM tbl_Category WHERE categoryid = @id";

            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@id", id);
            conn.ExecuteQuery(sql, d, false);
        }
        /// <summary>
        /// Cập nhật thông tin một danh mục
        /// </summary>
        public void update(CategoryVO cate)
        {
            string sqlInsert = "UPDATE tbl_Category SET CategoryName=@Name WHERE CategoryName=@Id";

            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@Id", cate.CategoryId.ToString());
            d.Add("@Name", cate.CategoryName);

            conn.ExecuteQuery(sqlInsert, d, false);
        }

    }
}